// .........................................................................
// Title: mergent.do
//
// Imports data on bond issuances from Mergent, including covenants data 
// .........................................................................

* -------------------------------------------
* Issuer data from Mergent
* -------------------------------------------

* Mergent issuer ids and names
use "$raw/mergent/mergent_combined_issue.dta", clear
keep COMPLETE_CUSIP ISSUER_ID PROSPECTUS_ISSUER_NAME
rename COMPLETE_CUSIP cusip
rename ISSUER_ID mergent_issuer_id
rename PROSPECTUS_ISSUER_NAME mergent_issuer_name
gsort mergent_issuer_id
by mergent_issuer_id: egen modal_name = mode(mergent_issuer_name), maxmode
replace mergent_issuer_name = modal_name
drop modal_name
gsort cusip
order cusip
save "$tmp/mergent_issuer_ids", replace

* Issue code to cusip crosswalk
use "$raw/mergent/mergent_combined_issue.dta", clear
foreach var of varlist * {
    local x = lower("`var'")
    rename `var' `x'
}
keep complete_cusip issue_id issuer_id
rename complete_cusip cusip
save "$tmp/mergent_cusip_to_ids", replace

* Lead underwriter data
use "$raw/mergent/fisd_issue_agents.dta", clear
mmerge issue_id using "$tmp/mergent_cusip_to_ids", unmatched(m)
drop _merge
drop issue_id issuer_id
keep if agent_type == "LU"
mmerge agent_id using "$raw/mergent/fisd_agent.dta", unmatched(m) ukeep(legal_name)
drop _merge
drop agent_type
order cusip agent_id legal_name
rename agent_id lu_id
rename legal_name lu_name
save "$tmp/mergent_lead_underwriters", replace

* covenants data
use "$raw/mergent/mergent_combined_issue.dta", clear
keep ISSUE_ID ISSUER_ID NEGATIVE_PLEDGE_COVENANT COVENANT_DEFEAS_WO_TAX_CONSEQ COVENANTS LEGAL_DEFEASANCE ///
    DEFEASANCE_TYPE DEFEASANCE_WO_TAX_CONSEQ CROSS_DEFAULT CROSS_ACCELERATION CHANGE_CONTROL_PUT_PROVISIONS ///
    RATING_DECLINE_TRIGGER_PUT DECLINING_NET_WORTH DECLINING_NET_WORTH_PERCENTAGE ///
    AFTER_ACQUIRED_PROPERTY_CLAUSE ECONOMIC_COV_DEF ASSET_SALE_CLAUSE ///
    CONSOLIDATION_MERGER INVESTMENTS MAINTENANCE_NET_WORTH RESTRICTED_PAYMENTS SALE_ASSETS SENIOR_DEBT_ISSUANCE STOCK_ISSUANCE_ISSUER ///
    STOCK_TRANSFER_SALE_DISP SUBORDINATED_DEBT_ISSUANCE ///
    TRANSACTION_AFFILIATES NET_EARNINGS_TEST_ISSUANCE COMPLETE_CUSIP LIENS_IS LIENS_SUB INDEBTEDNESS_IS INDEBTEDNESS_SUB ///
    FUNDED_DEBT_IS FUNDED_DEBT_SUB DIVIDENDS_RELATED_PAYMENTS_IS DIVIDENDS_RELATED_PAYMENTS_SUB SALES_LEASEBACK_IS SALES_LEASEBACK_SUB ///
    FIXED_CHARGE_COVERAGE_IS FIXED_CHARGE_COVERAGE_SUB LEVERAGE_TEST_IS LEVERAGE_TEST_SUB BORROWING_RESTRICTED
foreach var of varlist * {
    local x = lower("`var'")
    rename `var' `x'   
}
foreach var of varlist * {
    if ~inlist("`var'", "issue_id", "issuer_id", "prospectus_issuer_name", "complete_cusip", "voting_power_percentage", "voting_power_percentage_erp") & ///
        ~inlist("`var'", "declining_net_worth_trigger", "declining_net_worth_percentage") {
            di "`var'"
            replace `var' = "1" if `var' == "Y"
            replace `var' = "0" if `var' == "N"
            replace `var' = "0" if missing(`var')
    }
}
rename complete_cusip cusip

foreach var of varlist negative_pledge_covenant covenant_defeas_wo_tax_conseq covenants legal_defeasance defeasance_wo_tax_conseq cross_default cross_acceleration ///
    change_control_put_provisions rating_decline_trigger_put declining_net_worth after_acquired_property_clause economic_cov_def asset_sale_clause ///
    consolidation_merger investments maintenance_net_worth restricted_payments sale_assets senior_debt_issuance stock_issuance_issuer ///
    stock_transfer_sale_disp subordinated_debt_issuance transaction_affiliates net_earnings_test_issuance ///
    indebtedness_is indebtedness_sub liens_is liens_sub funded_debt_is funded_debt_sub dividends_related_payments_is dividends_related_payments_sub ///
    sales_leaseback_is sales_leaseback_sub fixed_charge_coverage_is fixed_charge_coverage_sub leverage_test_is leverage_test_sub borrowing_restricted {
    destring `var', replace
}

gen sales_leaseback = max(sales_leaseback_is, sales_leaseback_sub)
gen indebtedness = max(indebtedness_is, indebtedness_sub)
gen liens = max(liens_is, liens_sub)
gen funded_debt = max(funded_debt_is, funded_debt_sub)
gen dividends_related_payments = max(dividends_related_payments_is, dividends_related_payments_sub)
gen fixed_charge_coverage = max(fixed_charge_coverage_is, fixed_charge_coverage_sub)
gen leverage_test = max(leverage_test_is, leverage_test_sub)

save "$tmp/mergent_covenants", replace
